<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <title>MySQL 速查表</title>
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <style>
        :root{
            --bg:#0d1117;
            --fg:#c9d1d9;
            --accent:#58a6ff;
            --danger:#f85149;
            --success:#3fb950;
            --code-bg:#161b22;
            --border:#30363d;
            --hover:#1f2937;
            font-size:16px;
        }
        *,*::before,*::after{box-sizing:border-box}
        body{
            margin:0;
            padding:2rem;
            background:var(--bg);
            color:var(--fg);
            font-family:-apple-system,BlinkMacSystemFont,"Segoe UI",Helvetica,Arial,sans-serif;
            line-height:1.6;
        }
        h1,h2,h3{color:var(--accent);font-weight:600}
        h1{font-size:2rem;text-align:center;margin-bottom:2rem}
        h2{font-size:1.5rem;margin-top:2.5rem;margin-bottom:1rem;border-bottom:1px solid var(--border);padding-bottom:.3rem}
        h3{font-size:1.25rem;margin-top:2rem}
        a{color:var(--accent);text-decoration:none}
        a:hover{text-decoration:underline}
        code{
            background:var(--code-bg);
            color:var(--success);
            padding:.2em .4em;
            border-radius:6px;
            font-size:0.9em;
            font-family:Consolas,Monaco,"Ubuntu Mono",monospace
        }
        pre{overflow-x:auto;background:var(--code-bg);padding:1rem;border-radius:8px}
        table{width:100%;border-collapse:collapse;margin:1rem 0}
        th,td{padding:.75rem 1rem;border:1px solid var(--border)}
        th{background:var(--code-bg);color:var(--accent)}
        tr:nth-child(even){background:rgba(255,255,255,.03)}
        tr:hover{background:var(--hover)}
        hr{border:none;height:1px;background:var(--border);margin:2rem 0}
        ul{padding-left:1.25rem}
        li{margin:.25rem 0}
        ::selection{background:var(--accent);color:#fff}
    </style>
</head>
<body>

<h1>MySQL 速查表</h1>
<p style="text-align:center">
    <a href="SQL.md">← 返回 SQL 目录</a>
</p>

<!-- 目录 -->
<h2 id="mulu">目录</h2>
<ul>
    <li><a href="#find">mysql 查找显示</a></li>
    <li><a href="#col">查看列</a></li>
    <li><a href="#expr">算术表达式</a></li>
    <li><a href="#show-tables">查看数据库中表</a></li>
    <li><a href="#update">使用更新数据表</a></li>
    <li><a href="#ddl">DLL 操作</a></li>
    <li><a href="#dml">DML 数据操作语言</a></li>
    <li><a href="#dcl">DCL 数据控制语言</a></li>
    <li><a href="#tcl">TCL 事务控制语言</a></li>
    <li><a href="#alias">行列 / 别名</a></li>
    <li><a href="#idx">直接创建索引</a></li>
    <li><a href="#cond">条件</a></li>
    <li><a href="#logic">逻辑条件</a></li>
    <li><a href="#prio">优先规则</a></li>
</ul>

<!-- 正文 -->
<h2 id="find">MySQL 查找显示</h2>
<table>
    <thead><tr><th>名字</th><th>含义</th><th>例子</th></tr></thead>
    <tbody>
    <tr><td><code>FIND_IN_SET</code></td><td>查找字符串</td><td><code>FIND_IN_SET('a','a,b,c,d,e')</code></td></tr>
    <tr><td><code>DESCRIBE</code></td><td>显示表结构</td><td><code>DESCRIBE 表名</code></td></tr>
    <tr><td><code>EXPLAIN</code></td><td>执行计划</td><td><code>EXPLAIN SELECT * FROM 表名</code></td></tr>
    <tr><td><code>TRUNCATE</code></td><td>清空表</td><td><code>TRUNCATE TABLE 表名</code></td></tr>
    <tr><td><code>SELECT</code></td><td>投影列</td><td><code>SELECT 列1,列2 FROM 表名</code></td></tr>
    <tr><td><code>*</code></td><td>所有列</td><td><code>SELECT * FROM 表名</code></td></tr>
    <tr><td><code>DISTINCT</code></td><td>去重</td><td><code>SELECT DISTINCT 列 FROM 表名</code></td></tr>
    <tr><td><code>AS</code></td><td>别名</td><td><code>SELECT 列 AS 别名 FROM 表名</code></td></tr>
    </tbody>
</table>

<h2 id="col">查看列</h2>
<table>
    <thead><tr><th>命令</th><th>描述</th></tr></thead>
    <tbody>
    <tr><td><code>SELECT * FROM departments;</code></td><td>查看 departments 全部列</td></tr>
    <tr><td><code>SELECT department_name FROM departments;</code></td><td>查看指定列</td></tr>
    </tbody>
</table>

<h2 id="expr">算术表达式 & 排序/分组</h2>
<ul>
    <li><code>ORDER BY</code> 排序；<code>GROUP BY</code> 分组；<code>HAVING</code> 分组后筛选</li>
    <li>聚合：<code>COUNT / SUM / AVG / MAX / MIN</code></li>
    <li><code>ASC | DESC</code> 升/降序；<code>LIMIT</code> 限制条数</li>
</ul>
<table>
    <thead><tr><th>描述</th><th>示例</th></tr></thead>
    <tbody>
    <tr><td>年薪并升序</td><td><code>SELECT employee_id, last_name, 12*salary AS annsal FROM employees ORDER BY annsal;</code></td></tr>
    <tr><td>多列排序</td><td><code>SELECT department_id, salary FROM employees ORDER BY department_id ASC, salary DESC;</code></td></tr>
    <tr><td>分组后筛选</td><td><code>SELECT customer_id, COUNT(*) order_count FROM orders GROUP BY customer_id HAVING COUNT(*) > 1;</code></td></tr>
    </tbody>
</table>

<h2 id="show-tables">查看数据库中表</h2>
<table>
    <thead><tr><th>命令</th><th>描述</th></tr></thead>
    <tbody>
    <tr><td><code>SHOW TABLES;</code></td><td>当前库所有表</td></tr>
    <tr><td><code>SHOW COLUMNS FROM 表名;</code></td><td>表结构</td></tr>
    <tr><td><code>SHOW CREATE TABLE 表名;</code></td><td>建表语句</td></tr>
    </tbody>
</table>

<h2 id="update">使用/更新数据表</h2>
<table>
    <thead><tr><th>命令</th><th>描述</th></tr></thead>
    <tbody>
    <tr><td><code>USE 库名;</code></td><td>切换数据库</td></tr>
    <tr><td><code>RENAME TABLE 旧 TO 新;</code></td><td>重命名表</td></tr>
    </tbody>
</table>

<h2 id="ddl">DDL 操作</h2>
<table>
    <thead><tr><th>命令</th><th>描述</th><th>示例</th></tr></thead>
    <tbody>
    <tr><td><code>CREATE DATABASE</code></td><td>建库</td><td><code>CREATE DATABASE db CHARSET utf8mb4;</code></td></tr>
    <tr><td><code>ALTER DATABASE</code></td><td>修改字符集</td><td><code>ALTER DATABASE db CHARSET utf8mb4;</code></td></tr>
    <tr><td><code>ALTER TABLE ... ADD|CHANGE|MODIFY|DROP</code></td><td>改表结构</td><td><code>ALTER TABLE t ADD COLUMN age INT;</code></td></tr>
    <tr><td><code>DROP DATABASE</code></td><td>删库</td><td><code>DROP DATABASE myshrio;</code></td></tr>
    <tr><td><code>DROP SCHEMA database</code></td><td>删除整个库包括 表视图 存储</td><td><code>DROP SCHEMA myshrio;</code></td></tr>

    </tbody>
</table>

<h2 id="dml">DML 数据操作语言</h2>
<table>
    <thead><tr><th>命令</th><th>描述</th><th>示例</th></tr></thead>
    <tbody>
    <tr><td><code>INSERT INTO 表(列...) VALUES(值...);</code></td><td>指定列插入</td><td><code>INSERT INTO departments(name) VALUES('Market');</code></td></tr>
    <tr><td><code>UPDATE 表 SET 列=值 WHERE 条件;</code></td><td>更新</td><td><code>UPDATE emp3 SET address='北京' WHERE emp_id=1;</code></td></tr>
    <tr><td><code>DELETE FROM 表 WHERE 条件;</code></td><td>删除行</td><td><code>DELETE FROM emp3 WHERE emp_id=1;</code></td></tr>
    </tbody>
</table>

<h2 id="dcl">DCL 数据控制语言</h2>
<table>
    <thead><tr><th>命令</th><th>描述</th><th>示例</th></tr></thead>
    <tbody>
    <tr><td><code>GRANT</code></td><td>授权</td><td><code>GRANT ALL ON testdb.* TO 'u'@'%' IDENTIFIED BY 'pwd';</code></td></tr>
    <tr><td><code>REVOKE</code></td><td>回收</td><td><code>REVOKE ALL ON testdb.* FROM 'u'@'%';</code></td></tr>
    </tbody>
</table>

<h2 id="tcl">TCL 事务控制语言</h2>
<table>
    <thead><tr><th>命令</th><th>描述</th></tr></thead>
    <tbody>
    <tr><td><code>COMMIT;</code></td><td>提交事务</td></tr>
    <tr><td><code>ROLLBACK;</code></td><td>回滚事务</td></tr>
    <tr><td><code>SAVEPOINT sp;</code></td><td>设置回滚点</td></tr>
    </tbody>
</table>

<h2 id="alias">行列 / 别名 / 去重</h2>
<table>
    <thead><tr><th>描述</th><th>示例</th></tr></thead>
    <tbody>
    <tr><td>列别名</td><td><code>SELECT last_name AS name FROM employees;</code></td></tr>
    <tr><td>表别名</td><td><code>SELECT e.last_name FROM employees e;</code></td></tr>
    <tr><td>去重</td><td><code>SELECT DISTINCT department_id FROM employees;</code></td></tr>
    </tbody>
</table>

<h2 id="idx">直接创建索引</h2>
<table>
    <thead><tr><th>描述</th><th>示例</th></tr></thead>
    <tbody>
    <tr><td>创建索引</td><td><code>CREATE INDEX idx_last ON employees(last_name);</code></td></tr>
    <tr><td>删除索引</td><td><code>DROP INDEX idx_last ON employees;</code></td></tr>
    </tbody>
</table>

<h2 id="cond">比较条件</h2>
<table>
    <thead><tr><th>描述</th><th>示例</th></tr></thead>
    <tbody>
    <tr><td>区间</td><td><code>SELECT * FROM employees WHERE salary BETWEEN 3000 AND 8000;</code></td></tr>
    <tr><td>枚举</td><td><code>SELECT * FROM employees WHERE salary IN (5000,6000,8000);</code></td></tr>
    <tr><td>模糊</td><td><code>SELECT last_name FROM employees WHERE last_name LIKE '_e%';</code></td></tr>
    <tr><td>空值</td><td><code>SELECT * FROM employees WHERE commission_pct IS NULL;</code></td></tr>
    </tbody>
</table>

<h2 id="logic">逻辑条件</h2>
<table>
    <thead><tr><th>描述</th><th>示例</th></tr></thead>
    <tbody>
    <tr><td>AND</td><td><code>SELECT last_name FROM employees WHERE salary=8000 AND last_name LIKE '%e%';</code></td></tr>
    <tr><td>OR</td><td><code>SELECT last_name FROM employees WHERE salary=8000 OR last_name LIKE '%e%';</code></td></tr>
    <tr><td>NOT</td><td><code>SELECT last_name FROM employees WHERE last_name NOT LIKE '%u%';</code></td></tr>
    </tbody>
</table>

<h2 id="prio">优先规则</h2>
<p>标准优先级：<code>NOT → AND → OR</code>，可用括号强制顺序。</p>

<hr>
<p style="text-align:center">End of Cheatsheet ☕</p>
</body>
</html>
